<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class UserSubscribe extends Model
{
    //用戶預約表（订单快照）
    protected $table = 'user_subscribe';

    protected $fillable = [
      'user_id',
      'class_base_id',
      'store_id',
      'store_name',
      'class_classroom_id',
      'class_base_info_id',
      'class_classroom_name',
      'teacher_id',
      'teacher_name',
      'remarks',
      'class_time',
      'end_time',
      'subscribe_status',
      'buy_type',
      'class_info',
      'is_tutoring',
    ];

    public function user(){
        return $this->belongsTo(User::class,'user_id','id');
    }

    public function class_base(){
        return $this->belongsTo(ClassBase::class,'class_base_id','id');
    }

    public function store(){
        return $this->belongsTo(Store::class,'store_id','id');
    }

    public function classroom(){
        return $this->belongsTo(Classroom::class,'class_classroom_id','id');
    }

    public function class_base_info(){
        return $this->belongsTo(ClassBaseInfo::class,'class_base_info_id','id');
    }

    public function scopeWhereID($query, $id){
        return $query->where('id',$id);
    }

    public function scopeWhereUserID($query, $user_id){
        return $query->where('user_id',$user_id);
    }

    public function scopeOrderCreatedAt($query, $order){
        return $query->OrderBy('created_at',$order);
    }

    //獲取課室使用数量
    public function ClassUseNumber($class_classroom_id,$start_time,$end_time){
       $number = $this->where('class_classroom_id',$class_classroom_id)
           ->where('class_time','>=',$start_time)
           ->where('end_time','<=',$end_time)
           ->whereBetween('subscribe_status',[1,2])
           ->count();
        return $number;
    }

    /**
     * 7天內是否重複預約
     * @param $class_info_id
     * @param $start_time
     * @param $end_time
     * @return mixed
     */
    public function CheckSevenClassInfoNumber($class_info_id,$start_time,$end_time,$user_id){
        $number = $this->where('class_base_info_id',$class_info_id)
            ->where('user_id',$user_id)
            ->where('class_time','>=',$start_time)
            ->where('end_time','<=',$end_time)
            ->whereBetween('subscribe_status',[1,2])
            ->count();
        return $number;
    }

    /**
     * 課程安排預約人數
     * @param $class_base_info_id 課程安排ID
     * @param $start_time 查詢開始時間
     * @param $end_time 查詢結束時間
     * @return mixed
     */
    public function ClassSubscribeNumber($class_base_info_id, $start_time, $end_time){
        $number = $this->whereIn('class_base_info_id',$class_base_info_id)
            ->whereBetween('subscribe_status',[1,2])
            ->where('class_time','>=',$start_time)
            ->where('end_time','<=',$end_time)
            ->select([
                'class_base_info_id',
                DB::raw('count(*) as total')
            ])
            ->groupBy('class_base_info_id')
            ->get()
            ->toArray();
        $number = array_column($number,'total','class_base_info_id');
        return $number;
    }


}
